package cn.iocoder.yudao.module.system.service.examscore;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Component;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 动态表头解析器
 * 支持动态列位置检测和多层表头解析
 */
@Component
@Slf4j
public class DynamicHeaderParser {

    /**
     * 表头映射结果
     */
    public static class HeaderMapping {
        private final Map<String, Integer> fieldColumnMap = new HashMap<>();
        private final Map<String, String> fieldDisplayNameMap = new HashMap<>();
        private final Set<String> nonScoringSubjects = new HashSet<>();
        private String tableType; // PHYSICS 或 HISTORY
        
        public void addMapping(String fieldName, int columnIndex, String displayName) {
            fieldColumnMap.put(fieldName, columnIndex);
            fieldDisplayNameMap.put(fieldName, displayName);
        }
        
        public Integer getColumnIndex(String fieldName) {
            return fieldColumnMap.get(fieldName);
        }
        
        public String getDisplayName(String fieldName) {
            return fieldDisplayNameMap.get(fieldName);
        }
        
        public void addNonScoringSubject(String subject) {
            nonScoringSubjects.add(subject);
        }
        
        public Set<String> getNonScoringSubjects() {
            return nonScoringSubjects;
        }
        
        public String getTableType() {
            return tableType;
        }
        
        public void setTableType(String tableType) {
            this.tableType = tableType;
        }
        
        public Map<String, Integer> getAllMappings() {
            return new HashMap<>(fieldColumnMap);
        }
    }

    /**
     * 字段名称映射配置
     */
    private static final Map<String, List<String>> FIELD_PATTERNS = new HashMap<>();
    
    static {
        // 基础信息字段
        FIELD_PATTERNS.put("examNo", Arrays.asList("准考证号", "考号", "学号"));
        FIELD_PATTERNS.put("studentName", Arrays.asList("姓名", "学生姓名", "考生姓名"));
        FIELD_PATTERNS.put("className", Arrays.asList("班级", "班级名称", "所在班级"));
        FIELD_PATTERNS.put("subjectCombination", Arrays.asList("选考科目", "科目组合", "选科组合"));
        
        // 总分字段
        // 注意：单独的"总分"列应该优先匹配到 totalOriginalScore（原始分是必需的）
        FIELD_PATTERNS.put("totalOriginalScore", Arrays.asList("总分.*原始分", "总.*原始分", "原始总分", "总分"));
        FIELD_PATTERNS.put("totalAssignedScore", Arrays.asList("总分.*赋分", "总.*赋分", "赋分总分"));
        FIELD_PATTERNS.put("totalUnionRank", Arrays.asList("总分.*联考.*名次", "总分.*联考.*排名", "联考.*总分.*名次"));
        // 组合排名字段 - 加强匹配规则，避免误匹配（必须同时包含"总分"或明确的组合相关词）
        FIELD_PATTERNS.put("combinationRank", Arrays.asList(
            "总分.*组合.*名次", "总分.*组合.*排名", 
            "科目组合.*名次", "科目组合.*排名",
            "选科组合.*名次", "选科组合.*排名",
            "组合排名", "组合名次"
        ));
        FIELD_PATTERNS.put("schoolRank", Arrays.asList("总分.*校内.*名次", "总分.*校内.*排名", "校内.*总分.*名次"));
        FIELD_PATTERNS.put("classRank", Arrays.asList("总分.*班级.*名次", "总分.*班级.*排名", "班级.*总分.*名次"));
        
        // 各科目字段模式
        addSubjectPatterns("chinese", "语文");
        addSubjectPatterns("math", "数学");
        addSubjectPatterns("english", "英语");
        addSubjectPatterns("physics", "物理");
        addSubjectPatterns("chemistry", "化学");
        addSubjectPatterns("biology", "生物");
        addSubjectPatterns("politics", "政治");
        addSubjectPatterns("history", "历史");
        addSubjectPatterns("geography", "地理");
        addSubjectPatterns("russian", "俄语");
        addSubjectPatterns("japanese", "日语");
        addSubjectPatterns("spanish", "西班牙语");
        
        // 第一高分和第二高分字段 - 支持多级表头
        FIELD_PATTERNS.put("firstTopSubject", Arrays.asList(
            "第一高分.*科目", "第一.*科目", "第一高分科目", "第一高分-科目",
            ".*第一高分.*科目.*", ".*第一.*科目.*", ".*科目.*第一高分.*"
        ));
        FIELD_PATTERNS.put("firstTopOriginalScore", Arrays.asList(
            "第一高分.*原始分", "第一.*原始分", "第一高分原始分", "第一高分-原始分",
            ".*第一高分.*原始分.*", ".*第一.*原始分.*", ".*原始分.*第一高分.*"
        ));
        FIELD_PATTERNS.put("firstTopAssignedScore", Arrays.asList(
            "第一高分.*赋分", "第一.*赋分", "第一高分赋分", "第一高分-赋分",
            ".*第一高分.*赋分.*", ".*第一.*赋分.*", ".*赋分.*第一高分.*"
        ));
        FIELD_PATTERNS.put("firstTopUnionRank", Arrays.asList(
            "第一高分.*联考.*名次", "第一.*联考.*名次", "第一高分联考排名", "第一高分-联考排名",
            ".*第一高分.*联考.*名次.*", ".*第一.*联考.*名次.*", ".*联考.*名次.*第一高分.*"
        ));
        FIELD_PATTERNS.put("firstTopSchoolRank", Arrays.asList(
            "第一高分.*校内.*名次", "第一.*校内.*名次", "第一高分校内排名", "第一高分-校内排名",
            ".*第一高分.*校内.*名次.*", ".*第一.*校内.*名次.*", ".*校内.*名次.*第一高分.*"
        ));
        FIELD_PATTERNS.put("firstTopClassRank", Arrays.asList(
            "第一高分.*班级.*名次", "第一.*班级.*名次", "第一高分班级排名", "第一高分-班级排名",
            ".*第一高分.*班级.*名次.*", ".*第一.*班级.*名次.*", ".*班级.*名次.*第一高分.*"
        ));
        
        FIELD_PATTERNS.put("secondTopSubject", Arrays.asList(
            "第二高分.*科目", "第二.*科目", "第二高分科目", "第二高分-科目",
            ".*第二高分.*科目.*", ".*第二.*科目.*", ".*科目.*第二高分.*"
        ));
        FIELD_PATTERNS.put("secondTopOriginalScore", Arrays.asList(
            "第二高分.*原始分", "第二.*原始分", "第二高分原始分", "第二高分-原始分",
            ".*第二高分.*原始分.*", ".*第二.*原始分.*", ".*原始分.*第二高分.*"
        ));
        FIELD_PATTERNS.put("secondTopAssignedScore", Arrays.asList(
            "第二高分.*赋分", "第二.*赋分", "第二高分赋分", "第二高分-赋分",
            ".*第二高分.*赋分.*", ".*第二.*赋分.*", ".*赋分.*第二高分.*"
        ));
        FIELD_PATTERNS.put("secondTopUnionRank", Arrays.asList(
            "第二高分.*联考.*名次", "第二.*联考.*名次", "第二高分联考排名", "第二高分-联考排名",
            ".*第二高分.*联考.*名次.*", ".*第二.*联考.*名次.*", ".*联考.*名次.*第二高分.*"
        ));
        FIELD_PATTERNS.put("secondTopSchoolRank", Arrays.asList(
            "第二高分.*校内.*名次", "第二.*校内.*名次", "第二高分校内排名", "第二高分-校内排名",
            ".*第二高分.*校内.*名次.*", ".*第二.*校内.*名次.*", ".*校内.*名次.*第二高分.*"
        ));
        FIELD_PATTERNS.put("secondTopClassRank", Arrays.asList(
            "第二高分.*班级.*名次", "第二.*班级.*名次", "第二高分班级排名", "第二高分-班级排名",
            ".*第二高分.*班级.*名次.*", ".*第二.*班级.*名次.*", ".*班级.*名次.*第二高分.*"
        ));
    }
    
    /**
     * 为科目添加字段模式
     */
    private static void addSubjectPatterns(String subjectPrefix, String subjectName) {
        // 支持多级表头：科目名 + 具体字段名
        FIELD_PATTERNS.put(subjectPrefix + "OriginalScore", Arrays.asList(
            subjectName + ".*原始分", subjectName + "原始", 
            subjectName + "原始分", subjectName + "-原始分",
            ".*" + subjectName + ".*原始分.*", ".*原始分.*" + subjectName + ".*"
        ));
        FIELD_PATTERNS.put(subjectPrefix + "AssignedScore", Arrays.asList(
            subjectName + ".*赋分", subjectName + "赋分",
            subjectName + "-赋分", ".*" + subjectName + ".*赋分.*", ".*赋分.*" + subjectName + ".*"
        ));
        FIELD_PATTERNS.put(subjectPrefix + "GradeLevel", Arrays.asList(
            subjectName + ".*等级", subjectName + "等级", subjectName + "赋分等级",
            subjectName + "-等级", subjectName + "-赋分等级",
            ".*" + subjectName + ".*等级.*", ".*等级.*" + subjectName + ".*"
        ));
        FIELD_PATTERNS.put(subjectPrefix + "UnionRank", Arrays.asList(
            subjectName + ".*联考.*名次", subjectName + ".*联考.*排名", 
            subjectName + "联考排名", subjectName + "-联考排名",
            ".*" + subjectName + ".*联考.*名次.*", ".*" + subjectName + ".*联考.*排名.*"
        ));
        FIELD_PATTERNS.put(subjectPrefix + "SchoolRank", Arrays.asList(
            subjectName + ".*校内.*名次", subjectName + ".*校内.*排名",
            subjectName + "校内排名", subjectName + "-校内排名", 
            ".*" + subjectName + ".*校内.*名次.*", ".*" + subjectName + ".*校内.*排名.*"
        ));
        FIELD_PATTERNS.put(subjectPrefix + "ClassRank", Arrays.asList(
            subjectName + ".*班级.*名次", subjectName + ".*班级.*排名",
            subjectName + "班级排名", subjectName + "-班级排名",
            ".*" + subjectName + ".*班级.*名次.*", ".*" + subjectName + ".*班级.*排名.*"
        ));
    }

    /**
     * 解析表头并创建字段映射（支持多层表头）
     */
    public HeaderMapping parseHeaders(Row headerRow1, Row headerRow2) {
        HeaderMapping mapping = new HeaderMapping();
        
        // 收集所有表头信息
        List<String> headers = collectHeaders(headerRow1, headerRow2);
        
        // 检测表格类型
        String tableType = detectTableType(headers);
        mapping.setTableType(tableType);
        log.info("检测到表格类型: {}", tableType);
        
        // 解析字段映射
        parseFieldMappings(headers, mapping);
        
        // 检测非计分科目
        detectNonScoringSubjects(headers, mapping);
        
        log.info("表头解析完成，共映射{}个字段", mapping.getAllMappings().size());
        return mapping;
    }
    
    /**
     * 解析多层表头并创建字段映射
     */
    public HeaderMapping parseMultiLevelHeaders(List<Row> headerRows) {
        HeaderMapping mapping = new HeaderMapping();
        
        if (headerRows == null || headerRows.isEmpty()) {
            throw new IllegalArgumentException("表头行不能为空");
        }
        
        // 收集多层表头信息
        List<String> headers = collectMultiLevelHeaders(headerRows);
        
        // 检测表格类型
        String tableType = detectTableType(headers);
        mapping.setTableType(tableType);
        log.info("检测到表格类型: {}", tableType);
        
        // 解析字段映射
        parseFieldMappings(headers, mapping);
        
        // 检测非计分科目
        detectNonScoringSubjects(headers, mapping);
        
        log.info("多层表头解析完成，共映射{}个字段", mapping.getAllMappings().size());
        return mapping;
    }
    
    /**
     * 收集表头信息，支持多层表头
     */
    private List<String> collectHeaders(Row headerRow1, Row headerRow2) {
        List<String> headers = new ArrayList<>();
        
        if (headerRow1 == null && headerRow2 == null) {
            return headers;
        }
        
        int maxColumns = Math.max(
            headerRow1 != null ? headerRow1.getLastCellNum() : 0,
            headerRow2 != null ? headerRow2.getLastCellNum() : 0
        );
        
        for (int i = 0; i < maxColumns; i++) {
            String header1 = getCellStringValue(headerRow1, i);
            String header2 = getCellStringValue(headerRow2, i);
            
            // 合并多层表头
            String combinedHeader = combineHeaders(header1, header2);
            headers.add(combinedHeader);
        }
        
        return headers;
    }
    
    /**
     * 收集多层表头信息
     * 支持合并单元格：当某一层的单元格为空时，向左查找最近的非空单元格
     */
    private List<String> collectMultiLevelHeaders(List<Row> headerRows) {
        List<String> headers = new ArrayList<>();
        
        // 计算最大列数
        int maxColumns = headerRows.stream()
            .mapToInt(row -> row != null ? row.getLastCellNum() : 0)
            .max()
            .orElse(0);
        
        // 为每一行维护最后一个非空值（用于处理合并单元格）
        List<String> lastNonEmptyValues = new ArrayList<>(Collections.nCopies(headerRows.size(), ""));
        
        for (int i = 0; i < maxColumns; i++) {
            List<String> columnHeaders = new ArrayList<>();
            
            // 收集每一层的表头
            for (int rowIndex = 0; rowIndex < headerRows.size(); rowIndex++) {
                Row row = headerRows.get(rowIndex);
                String headerValue = getCellStringValue(row, i);
                
                if (headerValue != null && !headerValue.trim().isEmpty()) {
                    // 非空单元格：记录并使用
                    String trimmedValue = headerValue.trim();
                    lastNonEmptyValues.set(rowIndex, trimmedValue);
                    columnHeaders.add(trimmedValue);
                } else {
                    // 空单元格：使用该行最后一个非空值（处理合并单元格）
                    String lastValue = lastNonEmptyValues.get(rowIndex);
                    if (!lastValue.isEmpty()) {
                        columnHeaders.add(lastValue);
                    }
                }
            }
            
            // 组合多层表头
            String combinedHeader = combineMultiLevelHeaders(columnHeaders);
            headers.add(combinedHeader);
            
            // 调试：输出前20列的表头组合结果
            if (i < 20) {
                log.info("列{}: 层级={}, 组合结果='{}'", i, columnHeaders, combinedHeader);
            }
        }
        
        log.info("收集到{}列多层表头信息", headers.size());
        return headers;
    }
    
    /**
     * 组合多层表头信息
     */
    private String combineMultiLevelHeaders(List<String> headerLevels) {
        if (headerLevels.isEmpty()) {
            return "";
        }
        
        if (headerLevels.size() == 1) {
            return headerLevels.get(0);
        }
        
        // 去重并保持顺序
        List<String> uniqueHeaders = new ArrayList<>();
        for (String header : headerLevels) {
            if (!uniqueHeaders.contains(header)) {
                uniqueHeaders.add(header);
            }
        }
        
        // 智能组合表头
        if (uniqueHeaders.size() == 1) {
            return uniqueHeaders.get(0);
        }
        
        // 检查是否有包含关系
        String longestHeader = uniqueHeaders.stream()
            .max(Comparator.comparing(String::length))
            .orElse("");
        
        boolean allContained = uniqueHeaders.stream()
            .allMatch(header -> longestHeader.contains(header) || header.contains(longestHeader));
        
        if (allContained) {
            return longestHeader;
        }
        
        // 使用分隔符连接
        return String.join("-", uniqueHeaders);
    }
    
    /**
     * 合并多层表头
     */
    private String combineHeaders(String header1, String header2) {
        if (header1 == null || header1.trim().isEmpty()) {
            return header2 != null ? header2.trim() : "";
        }
        if (header2 == null || header2.trim().isEmpty()) {
            return header1.trim();
        }
        
        header1 = header1.trim();
        header2 = header2.trim();
        
        // 如果第二层表头包含第一层表头，直接使用第二层
        if (header2.contains(header1)) {
            return header2;
        }
        
        // 如果第一层表头包含第二层表头，直接使用第一层
        if (header1.contains(header2)) {
            return header1;
        }
        
        // 否则合并两层表头
        return header1 + header2;
    }
    
    /**
     * 检测表格类型（物理方向或历史方向）
     */
    private String detectTableType(List<String> headers) {
        for (String header : headers) {
            if (header != null) {
                if (header.contains("物理")) {
                    return "PHYSICS";
                } else if (header.contains("历史")) {
                    return "HISTORY";
                }
            }
        }
        
        // 默认返回物理方向
        log.warn("无法检测表格类型，默认使用物理方向");
        return "PHYSICS";
    }
    
    /**
     * 解析字段映射
     * 注意：为了避免"总分"这种通用列名被错误匹配，需要先匹配更具体的字段
     */
    private void parseFieldMappings(List<String> headers, HeaderMapping mapping) {
        // 用于记录已经映射的列，避免重复映射
        Set<Integer> mappedColumns = new HashSet<>();
        
        // 第一遍：优先匹配具体的字段（带有明确限定词的）
        for (int i = 0; i < headers.size(); i++) {
            String header = headers.get(i);
            if (header == null || header.trim().isEmpty()) {
                continue;
            }
            
            // 尝试匹配每个字段模式
            for (Map.Entry<String, List<String>> entry : FIELD_PATTERNS.entrySet()) {
                String fieldName = entry.getKey();
                List<String> patterns = entry.getValue();
                
                // 检查该字段是否已经被映射过
                if (mapping.getColumnIndex(fieldName) != null) {
                    continue;
                }
                
                // 第一遍只匹配具体的模式（包含.*或长度>2的模式）
                for (String pattern : patterns) {
                    if (pattern.contains(".*") || pattern.length() > 4) {
                        if (matchesPattern(header, pattern)) {
                            if (!mappedColumns.contains(i)) {
                                mapping.addMapping(fieldName, i, header);
                                mappedColumns.add(i);
                                log.debug("字段映射(具体): {} -> 列{} ({})", fieldName, i, header);
                                
                                if (fieldName.contains("russian") || fieldName.contains("japanese") || fieldName.contains("spanish")) {
                                    log.info("小语种字段识别成功: 表头='{}', 字段名='{}', 列索引={}", header, fieldName, i);
                                } else if (fieldName.equals("combinationRank") || fieldName.equals("subjectCombination") || 
                                          fieldName.equals("totalOriginalScore") || fieldName.equals("totalAssignedScore")) {
                                    log.info("关键字段识别: 表头='{}', 字段名='{}', 列索引={}", header, fieldName, i);
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }
        
        // 第二遍：匹配通用的字段（如"总分"、"姓名"等简短模式）
        for (int i = 0; i < headers.size(); i++) {
            String header = headers.get(i);
            if (header == null || header.trim().isEmpty() || mappedColumns.contains(i)) {
                continue;
            }
            
            boolean matched = false;
            for (Map.Entry<String, List<String>> entry : FIELD_PATTERNS.entrySet()) {
                String fieldName = entry.getKey();
                List<String> patterns = entry.getValue();
                
                if (mapping.getColumnIndex(fieldName) != null) {
                    continue;
                }
                
                // 第二遍匹配简短的通用模式
                for (String pattern : patterns) {
                    if (!pattern.contains(".*") && pattern.length() <= 4) {
                        if (matchesPattern(header, pattern)) {
                            mapping.addMapping(fieldName, i, header);
                            mappedColumns.add(i);
                            matched = true;
                            log.debug("字段映射(通用): {} -> 列{} ({})", fieldName, i, header);
                            
                            if (fieldName.equals("totalOriginalScore") || fieldName.equals("totalAssignedScore")) {
                                log.info("关键字段识别: 表头='{}', 字段名='{}', 列索引={}", header, fieldName, i);
                            }
                            break;
                        }
                    }
                }
                if (matched) break;
            }
            
            if (!matched) {
                log.info("未识别的表头: 列{} = '{}'", i, header);
            }
        }
        
        // 输出所有识别到的字段映射，用于调试
        log.info("所有字段映射: {}", mapping.getAllMappings());
        log.info("共映射{}个字段到{}列", mapping.getAllMappings().size(), mappedColumns.size());
        
        // 检查核心科目的映射情况
        String[] coreSubjects = {"chinese", "math", "english"};
        for (String subject : coreSubjects) {
            Integer originalCol = mapping.getColumnIndex(subject + "OriginalScore");
            Integer unionRankCol = mapping.getColumnIndex(subject + "UnionRank");
            Integer schoolRankCol = mapping.getColumnIndex(subject + "SchoolRank");
            Integer classRankCol = mapping.getColumnIndex(subject + "ClassRank");
            
            log.info("核心科目[{}]映射: 原始分列={}, 联考排名列={}, 校内排名列={}, 班级排名列={}", 
                    subject, originalCol, unionRankCol, schoolRankCol, classRankCol);
        }
    }
    
    /**
     * 检查表头是否匹配任何模式
     */
    private boolean matchesAnyPattern(String header, List<String> patterns) {
        for (String pattern : patterns) {
            if (matchesPattern(header, pattern)) {
                return true;
            }
        }
        return false;
    }
    
    /**
     * 检查表头是否匹配指定模式
     */
    private boolean matchesPattern(String header, String pattern) {
        try {
            Pattern regex = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE);
            Matcher matcher = regex.matcher(header);
            return matcher.find();
        } catch (Exception e) {
            // 如果正则表达式失败，使用简单的包含匹配
            return header.contains(pattern);
        }
    }
    
    /**
     * 检测非计分科目
     */
    private void detectNonScoringSubjects(List<String> headers, HeaderMapping mapping) {
        // 检测包含"不计分"、"非计分"等关键词的科目
        Pattern nonScoringPattern = Pattern.compile("(不计分|非计分|免考|缺考)", Pattern.CASE_INSENSITIVE);
        
        for (String header : headers) {
            if (header != null && nonScoringPattern.matcher(header).find()) {
                // 提取科目名称
                String subject = extractSubjectName(header);
                if (subject != null) {
                    mapping.addNonScoringSubject(subject);
                    log.info("检测到非计分科目: {}", subject);
                }
            }
        }
    }
    
    /**
     * 从表头中提取科目名称
     */
    private String extractSubjectName(String header) {
        String[] subjects = {"语文", "数学", "英语", "物理", "化学", "生物", "政治", "历史", "地理", "俄语", "日语", "西班牙语"};
        
        for (String subject : subjects) {
            if (header.contains(subject)) {
                return subject;
            }
        }
        
        return null;
    }
    
    /**
     * 获取单元格字符串值
     */
    private String getCellStringValue(Row row, int columnIndex) {
        if (row == null) {
            return null;
        }
        
        Cell cell = row.getCell(columnIndex);
        if (cell == null) {
            return null;
        }
        
        try {
            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    return String.valueOf((long) cell.getNumericCellValue());
                case BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                case FORMULA:
                    return cell.getCellFormula();
                default:
                    return null;
            }
        } catch (Exception e) {
            log.warn("读取单元格值失败: 行{}, 列{}, 错误: {}", row.getRowNum(), columnIndex, e.getMessage());
            return null;
        }
    }
    
    /**
     * 验证必需字段是否都已映射
     * 注意：
     * 1. 高一学生可能还没有选科，所以 subjectCombination 不是必需的
     * 2. 很多表格只有一个"总分"列（原始分），没有单独的赋分列，所以 totalAssignedScore 不是必需的
     */
    public boolean validateRequiredFields(HeaderMapping mapping) {
        String[] requiredFields = {
            "examNo", "studentName", "className",
            "totalOriginalScore"  // 只要求原始总分，赋分总分是可选的
        };
        
        List<String> missingFields = new ArrayList<>();
        for (String field : requiredFields) {
            if (mapping.getColumnIndex(field) == null) {
                missingFields.add(field);
            }
        }
        
        if (!missingFields.isEmpty()) {
            log.error("缺少必需字段映射: {}", missingFields);
            return false;
        }
        
        // 以下是可选字段，如果缺失只记录警告
        if (mapping.getColumnIndex("subjectCombination") == null) {
            log.warn("未找到选科组合字段，可能是高一年级或未选科学生");
        }
        
        if (mapping.getColumnIndex("totalAssignedScore") == null) {
            log.warn("未找到赋分总分字段，将使用原始分作为总分");
        }
        
        return true;
    }
    
    /**
     * 验证表头映射的完整性和正确性
     */
    public ValidationResult validateHeaderMapping(HeaderMapping mapping) {
        ValidationResult result = new ValidationResult();
        
        // 1. 验证必需字段
        if (!validateRequiredFields(mapping)) {
            result.addError("缺少必需的字段映射");
        }
        
        // 2. 验证科目字段
        validateSubjectFields(mapping, result);
        
        // 3. 验证列索引的有效性
        validateColumnIndices(mapping, result);
        
        // 4. 验证表格类型
        if (mapping.getTableType() == null || mapping.getTableType().trim().isEmpty()) {
            result.addWarning("未能检测到表格类型，将使用默认值");
        }
        
        // 5. 验证字段映射的重复性
        validateDuplicateMappings(mapping, result);
        
        return result;
    }
    
    /**
     * 验证科目字段映射
     */
    private void validateSubjectFields(HeaderMapping mapping, ValidationResult result) {
        String[] coreSubjects = {"chinese", "math", "english"};
        String[] selectiveSubjects = {"physics", "chemistry", "biology", "history", "geography", "politics"};
        String[] foreignLanguages = {"russian", "japanese", "spanish"};
        
        // 验证核心科目
        for (String subject : coreSubjects) {
            String originalField = subject + "OriginalScore";
            String assignedField = subject + "AssignedScore";
            
            if (mapping.getColumnIndex(originalField) == null && mapping.getColumnIndex(assignedField) == null) {
                result.addWarning("核心科目 " + subject + " 的成绩字段未找到");
            }
        }
        
        // 验证选择性科目（根据表格类型）
        int foundSelectiveSubjects = 0;
        for (String subject : selectiveSubjects) {
            String originalField = subject + "OriginalScore";
            if (mapping.getColumnIndex(originalField) != null) {
                foundSelectiveSubjects++;
            }
        }
        
        if (foundSelectiveSubjects == 0) {
            result.addWarning("未找到任何选择性科目的成绩字段");
        }
        
        // 验证外语科目
        int foundForeignLanguages = 0;
        for (String language : foreignLanguages) {
            String originalField = language + "OriginalScore";
            if (mapping.getColumnIndex(originalField) != null) {
                foundForeignLanguages++;
            }
        }
        
        if (foundForeignLanguages > 0) {
            result.addInfo("检测到 " + foundForeignLanguages + " 种外语科目");
        }
    }
    
    /**
     * 验证列索引的有效性
     */
    private void validateColumnIndices(HeaderMapping mapping, ValidationResult result) {
        Map<String, Integer> allMappings = mapping.getAllMappings();
        Set<Integer> usedIndices = new HashSet<>();
        
        for (Map.Entry<String, Integer> entry : allMappings.entrySet()) {
            Integer columnIndex = entry.getValue();
            if (columnIndex == null) {
                continue;
            }
            
            // 检查列索引是否为负数
            if (columnIndex < 0) {
                result.addError("字段 " + entry.getKey() + " 的列索引无效: " + columnIndex);
            }
            
            // 检查列索引是否重复
            if (usedIndices.contains(columnIndex)) {
                result.addWarning("列索引 " + columnIndex + " 被多个字段使用");
            } else {
                usedIndices.add(columnIndex);
            }
        }
    }
    
    /**
     * 验证字段映射的重复性
     */
    private void validateDuplicateMappings(HeaderMapping mapping, ValidationResult result) {
        Map<String, Integer> allMappings = mapping.getAllMappings();
        Map<Integer, List<String>> indexToFields = new HashMap<>();
        
        // 按列索引分组字段
        for (Map.Entry<String, Integer> entry : allMappings.entrySet()) {
            Integer columnIndex = entry.getValue();
            if (columnIndex != null) {
                indexToFields.computeIfAbsent(columnIndex, k -> new ArrayList<>()).add(entry.getKey());
            }
        }
        
        // 检查重复映射
        for (Map.Entry<Integer, List<String>> entry : indexToFields.entrySet()) {
            if (entry.getValue().size() > 1) {
                result.addError("列 " + entry.getKey() + " 被映射到多个字段: " + entry.getValue());
            }
        }
    }
    
    /**
     * 验证结果类
     */
    public static class ValidationResult {
        private final List<String> errors = new ArrayList<>();
        private final List<String> warnings = new ArrayList<>();
        private final List<String> infos = new ArrayList<>();
        
        public void addError(String message) {
            errors.add(message);
        }
        
        public void addWarning(String message) {
            warnings.add(message);
        }
        
        public void addInfo(String message) {
            infos.add(message);
        }
        
        public boolean hasErrors() {
            return !errors.isEmpty();
        }
        
        public boolean hasWarnings() {
            return !warnings.isEmpty();
        }
        
        public List<String> getErrors() {
            return new ArrayList<>(errors);
        }
        
        public List<String> getWarnings() {
            return new ArrayList<>(warnings);
        }
        
        public List<String> getInfos() {
            return new ArrayList<>(infos);
        }
        
        public boolean isValid() {
            return errors.isEmpty();
        }
        
        public String getSummary() {
            StringBuilder sb = new StringBuilder();
            if (!errors.isEmpty()) {
                sb.append("错误: ").append(errors).append("; ");
            }
            if (!warnings.isEmpty()) {
                sb.append("警告: ").append(warnings).append("; ");
            }
            if (!infos.isEmpty()) {
                sb.append("信息: ").append(infos);
            }
            return sb.toString();
        }
    }
    
    /**
     * 获取字段的安全列索引（如果字段不存在返回-1）
     */
    public int getSafeColumnIndex(HeaderMapping mapping, String fieldName) {
        Integer index = mapping.getColumnIndex(fieldName);
        return index != null ? index : -1;
    }
}